package com.dayouzi.esaydata.utils;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;

/**
 * 数据表转换实体类工具
 * 主函数中传入相关参数即可
 */
public class TableToDto {
    //mysql的ip地址：端口号
    private String url;
    //mysql的用户名
    private String userName;
    //mysql的密码
    private String passWord;
    //连接的数据库
    private String dataBase;
    //连接的表集合
    private ArrayList<String> tableNameList;
    //实体类放置的文件夹
    private String filePath;

    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public TableToDto() {
    }

    //有参构造方法
    public TableToDto(String url, String userName, String passWord, String dataBase,
                      ArrayList<String> tableNameList) {
        this.url = url;
        this.userName = userName;
        this.passWord = passWord;
        this.dataBase = dataBase;
        this.tableNameList = tableNameList;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }

    public String getDataBase() {
        return dataBase;
    }

    public void setDataBase(String dataBase) {
        this.dataBase = dataBase;
    }

    public ArrayList<String> gettableNameList() {
        return tableNameList;
    }

    public void settableNameList(ArrayList<String> tableNameList) {
        this.tableNameList = tableNameList;
    }

    public void tableToExcel() {
        String demo01 =
                "import lombok.AllArgsConstructor;\n" +
                        "import lombok.Data;\n" +
                        "import lombok.NoArgsConstructor;\n\n" +
                        "@Data\n" +
                        "@AllArgsConstructor\n" +
                        "@NoArgsConstructor\n" +
                        "public class ";
        String demo02 = "{\n";
        String demo03 = "}";

        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        //数据库连接路径
        url = "jdbc:mysql://" + url + "/" + dataBase + "?characterEncoding=utf-8&serverTimezone=GMT%2B8";
        //数据库连接
        Connection connection = null;
        //预编译处理
        PreparedStatement ps = null;
        //结果集
        ResultSet rs = null;
        try {
            //通过类加载机制，加载MySQL数据库连接驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接数据库
            connection = DriverManager.getConnection(url, userName, passWord);
            //遍历表名的集合
            ArrayList<String> tableNameListFinal = new ArrayList<>();
            if (tableNameList.size() == 1 && tableNameList.get(0).equals("AllTable")) {
                String sqlFindAllTableName = "show tables;";
                //对SQL语句进行预编译
                ps = connection.prepareStatement(sqlFindAllTableName);
                //执行SQL，获取结果集
                rs = ps.executeQuery();
                //遍历结果集
                while (rs.next()) {
                    tableNameListFinal.add(rs.getString(1));
                }
            } else {
                tableNameListFinal = tableNameList;
            }
            for (String tableName : tableNameListFinal) {
                //用分割src把package拿到，如果是springboot项目,则换为java
                String splitToPackAge = "src";
                String packAge = filePath.substring(filePath.lastIndexOf(splitToPackAge) + splitToPackAge.length() + 1);
                while (packAge.contains("\\") || packAge.contains("/")) {
                    packAge = packAge.replace("\\", ".").replace("/", ".");
                }
                StringBuilder AllString = new StringBuilder("package " + packAge + ";\n\n" + demo01 + tableName + "DTO" + demo02);
                //查询该表的所有字段信息
                String sqlFindTableName = "select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS" +
                        " where table_name = ? and table_schema = ? ";
                //对SQL语句进行预编译
                ps = connection.prepareStatement(sqlFindTableName);
                //对SQL语句中的占位符进行设置
                ps.setString(1, tableName);
                ps.setString(2, dataBase);
                //执行SQL，获取结果集
                rs = ps.executeQuery();
                //遍历结果集
                while (rs.next()) {
                    //该表的所有字段信息，用','分开
                    String COLUMN_NAME = rs.getString(1);
                    String DATA_TYPE = rs.getString(2).toLowerCase();
                    String replace = "";
                    switch (DATA_TYPE) {
                        case "char":
                        case "varchar":
                        case "text":
                        case "longtext":
                            replace = "String";
                            break;
                        case "int":
                            replace = "Integer";
                            break;
                        case "tinyint":
                        case "samllint":
                            replace = "Integer";
                            break;
                        case "float":
                            replace = "Float";
                            break;
                        case "double":
                            replace = "Double";
                            break;
                        case "date":
                            replace = "Date";
                            break;
                        case "time":
                            replace = "Time";
                            break;
                        case "timestamp":
                            replace = "DateTime";
                            break;
                    }
                    //下划线转换驼峰命名
                    COLUMN_NAME = underLineToCamelhump(COLUMN_NAME);
                    String line = "\tprivate " + replace + " " + COLUMN_NAME + ";\n";
                    AllString.append(line);
                }
                AllString.append(demo03);
                //获得读写流
                FileWriter fw = new FileWriter(filePath + "/" + tableName + "DTO.java");
                BufferedWriter bw = new BufferedWriter(fw);
                bw.write(AllString.toString());
                bw.flush();
                bw.close();
                fw.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 下划线形式转驼峰命名
     *
     * @param column
     */
    public static String underLineToCamelhump(String column) {
        StringBuilder result = new StringBuilder();
        String[] split = column.split("_");
        for (String s : split) {
            if (result.length() == 0) {
                result.append(s.toLowerCase());
            } else {
                result.append(s.substring(0, 1).toUpperCase());
                result.append(s.substring(1).toLowerCase());
            }
        }
        return result.toString();
    }


    public static void main(String[] args) {
        ArrayList<String> list = new ArrayList<String>();
        Collections.addAll(list, "user_admin"); //默认是当前数据库下的所有表，也可以指定某些表
//        Collections.addAll(list, "sichuan_yeji_gfz_certificate"); //默认是当前数据库下的所有表，也可以指定某些表
//        Collections.addAll(list, "sichuan_yeji_gfz_person"); //默认是当前数据库下的所有表，也可以指定某些表
        TableToDto tableToExcel0421 = new TableToDto("localhost:3306",
                "root", "123456", "flac", list);
        tableToExcel0421.setFilePath("D:\\ideaProjects\\flac\\service-flac\\src\\main\\java\\com\\flac\\flacservice\\pojo\\entity");
        tableToExcel0421.tableToExcel();
        System.out.println("生成完毕");
    }
}